package arts.spbu.testswing;

import java.sql.*;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: anton
 * Date: 30.11.12
 * Time: 10:23
 */

public class DBtest {
    private Connection cn;
    private Statement st;

    public DBtest() {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            System.out.println("Error: unable to load driver class");
            System.exit(1);
        }
        cn = null;
        try {
            cn = DriverManager.getConnection("jdbc:sqlite:text_annot.db");
            System.out.println("Connected to DB");

            st = cn.createStatement();
            st.setQueryTimeout(30);

            //st.execute("PRAGMA auto_vacuum = 1");
            st.execute("VACUUM");

            st.executeUpdate("DROP TABLE IF EXISTS annot");  //  Только для отладки
            st.executeUpdate("DROP TABLE IF EXISTS type");   //  Только для отладки
            st.executeUpdate("DROP TABLE IF EXISTS text");   //  Только для отладки
            st.executeUpdate("DROP TABLE IF EXISTS attr");   //  Только для отладки
            st.executeUpdate("DROP TABLE IF EXISTS ann_attr");   //  Только для отладки
            st.executeUpdate("CREATE TABLE IF NOT EXISTS type (id INTEGER PRIMARY KEY, name STRING NOT NULL)");

            //System.out.println("TABLE type created!");

            st.executeUpdate("CREATE TABLE IF NOT EXISTS attr (id INTEGER PRIMARY KEY, name STRING NOT NULL)");  // '-' - name для атрибутов рег. выражеий!
            st.executeUpdate("CREATE UNIQUE INDEX IF NOT EXISTS attr_index ON attr(name)");

            //System.out.println("TABLE attr created!");

            st.executeUpdate("CREATE TABLE IF NOT EXISTS text (id INTEGER PRIMARY KEY, path STRING NOT NULL)");
            st.executeUpdate("CREATE UNIQUE INDEX IF NOT EXISTS text_index ON text(path)");

            //System.out.println("TABLE text created!");

            st.executeUpdate("CREATE TABLE IF NOT EXISTS annot (id INTEGER PRIMARY KEY, start_pos INTEGER NOT NULL, end_pos INTEGER NOT NULL, text STRING NOT NULL, querytext STRING NOT NULL, type_id INTEGER NOT NULL, text_id INTEGER NOT NULL, FOREIGN KEY (type_id) REFERENCES type(id), FOREIGN KEY (text_id) REFERENCES text(id))");
            st.executeUpdate("CREATE UNIQUE INDEX IF NOT EXISTS annot_index ON annot(start_pos, end_pos, text, querytext, type_id, text_id)");

            //System.out.println("TABLE annot created!");

            st.executeUpdate("CREATE TABLE IF NOT EXISTS ann_attr (id INTEGER PRIMARY KEY, ann_id INTEGER NOT NULL, attr_id INTEGER NOT NULL, FOREIGN KEY (attr_id) REFERENCES attr(id), FOREIGN KEY (ann_id) REFERENCES annot(id))");
            st.executeUpdate("CREATE UNIQUE INDEX IF NOT EXISTS ann_attr_index ON ann_attr(ann_id, attr_id)");

            //System.out.println("TABLE ann_attr created!");

            st.executeUpdate("INSERT INTO type(name) VALUES ('Рег. выражение')"); //  Только для отладки (должно вызываться только 1 раз в самом начале)
            st.executeUpdate("INSERT INTO type(name) VALUES ('Словоформа')"); //  Только для отладки (должно вызываться только 1 раз в самом начале)
        } catch (SQLException e) {
            System.out.println("Error while creating db: " + e.getMessage());
        }
    }

    public void insertAttrs(HashSet<String> attrs_set, boolean isRegMode) {
        if (attrs_set == null) {
            System.out.println("Error: empty attributes list - unable to insert attrs!");
            return;
        }
        try {
            if (isRegMode) {
                st.executeUpdate("INSERT OR IGNORE INTO attr(name) VALUES('-')");
                //tempView("attr", 2);

                return;
            }
            PreparedStatement stprep = cn.prepareStatement("INSERT OR IGNORE INTO attr(name) VALUES(?)");
            for (String item : attrs_set) {
                stprep.setString(1, item);
                stprep.addBatch();
            }
            stprep.executeBatch();

            //tempView("attr", 2);

        } catch (SQLException e) {
            System.out.println("Error while inserting attrs into db: " + e.getMessage());
        }
    }

   /* public void tempView(String tableName, int colCount) {
        try {
            ResultSet rs = st.executeQuery("SELECT * FROM " + tableName);
            System.out.println("============================================");
            System.out.println("Current state of " + tableName + " table: ");
            while (rs.next()) {
                System.out.print("id = " + rs.getInt(1));
                for (int i = 2; i <= colCount; i ++)
                    System.out.print(" col" + i + " = " + rs.getString(i));
                System.out.println();
            }
            System.out.println("============================================");
        } catch (SQLException e) {
            System.out.println("Temp view error: " + e.getMessage());
        }
    }    */

    /*public void tempDubl(String tableName, int colCount, String params) {
        try {
            String sql = "SELECT * FROM " + tableName + " WHERE id NOT IN (SELECT min_id FROM (SELECT MIN(id) AS min_id, " + params + " FROM " + tableName + " GROUP BY " + params + "))";
            ResultSet rs = st.executeQuery(sql);
            System.out.println("============================================");
            System.out.println("Dublicates in " + tableName + " table: ");
            while (rs.next()) {
                //System.out.print("id = " + rs.getInt(1));
                for (int i = 2; i <= colCount - 1; i ++)
                    System.out.print(" col" + i + " = " + rs.getString(i));
                System.out.println();
            }
            System.out.println("============================================");
        } catch (SQLException e) {
            System.out.println("Temp view error: " + e.getMessage());
        }
    } */

    public void createRefs(ArrayList<WordAttrs> attrs_info, boolean isRegMode) {
        if (attrs_info == null) {
            System.out.println("Error: empty attributes list - unable to create references!");
            return;
        }
        try {
            PreparedStatement stprep = cn.prepareStatement("INSERT OR IGNORE INTO ann_attr(ann_id, attr_id) SELECT annot.id, attr.id FROM annot, attr WHERE annot.start_pos = ? AND annot.text = ? AND attr.name = ? AND annot.type_id = ?");
            for (WordAttrs item : attrs_info) {
                stprep.setString(1, String.valueOf(item.getStartPos()));
                stprep.setString(2, item.getWord());
                if (isRegMode) {
                    stprep.setString(3, "-");
                    stprep.setInt(4, 1);
                }
                else {
                    stprep.setString(3,item.getAttrs());
                    stprep.setInt(4, 2);
                }
                stprep.addBatch();
            }
            stprep.executeBatch();

        } catch (SQLException e) {
            System.out.println("Error while creating references between attrs and annot: " + e.getMessage());
            //e.printStackTrace();
        }
    }

    private Integer insertPath(String textPath) {
        if (textPath.equals(""))
            return 0;
        int text_id = 0;
        try {
            st.executeUpdate("INSERT OR IGNORE INTO text (path) VALUES ('" + textPath + "')");
        } catch (SQLException e) {
            System.out.println("Error while inserting file path data into db: " + e.getMessage());
        }

        try (ResultSet rs = st.executeQuery("SELECT ID FROM text WHERE path = '" + textPath + "'")) {
            if (rs == null) {
                System.out.println("Error: text path " + textPath + " not found in the database!");
                return 0;
            }
            text_id = rs.getInt(1);
        } catch (SQLException ex) {
            System.out.println("Error while inserting file path data into db: " + ex.getMessage());
        }
        return text_id;
    }

    // Добавить аннотации по результатам запроса пользователя
    public void insertData(List<String> alldata, String textPath) {
        if (alldata == null)
            return;
        int text_id = insertPath(textPath);
        if (text_id == 0) {
            System.out.println("Null text id!");
            return;
        }
        try {
            String sql = "INSERT OR IGNORE INTO annot(start_pos, end_pos, text, querytext, type_id, text_id)";
            for (String item : alldata)
                sql += "SELECT " + item + ", '" + text_id + "' UNION ALL ";
            if (sql.endsWith(" UNION ALL "))
                sql = sql.substring(0, sql.length() - " UNION ALL ".length());
            st.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("Error while inserting data into db: " + e.getMessage());
        }
    }

    // Добавить аннотации по результатам запроса пользователя
    public void insertDataBatch(List<ArrayList<String>> alldata, String textPath) {
        if (alldata == null)
            return;
        int text_id = insertPath(textPath);
        if (text_id == 0) {
            System.out.println("Null text id!");
            return;
        }
        try (PreparedStatement stprep = cn.prepareStatement("INSERT OR IGNORE INTO annot(start_pos, end_pos, text, querytext, type_id, text_id) VALUES(?, ?, ?, ?, ?, ?)")) {
            for (ArrayList<String> items : alldata) {
                stprep.setInt(1, Integer.parseInt(items.get(0)));
                stprep.setInt(2, Integer.parseInt(items.get(1)));
                stprep.setString(3, items.get(2));
                stprep.setString(4, items.get(3));
                stprep.setInt(5, Integer.parseInt(items.get(4)));
                stprep.setInt(6, text_id);
                stprep.addBatch();
                //System.out.println("Batch done successfully!");
            }
            stprep.executeBatch();
        } catch (SQLException e) {
            System.out.println("Error while inserting data into db: " + e.getMessage());
        }
    }

    // Удалить дубликаты после добавления в таблицу аннотаций новых записей
    /*private void deleteDuplicates(String tableName, String params) {
        try {
            String sql = "DELETE FROM " + tableName + " WHERE id NOT IN (SELECT min_id FROM (SELECT MIN(id) AS min_id, " + params + " FROM " + tableName + " GROUP BY " + params + "))";
            st.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("Error while deleting data from db: " + e.getMessage());
        }
    }  */

    // Показать все записи из таблицы аннотаций
    public ArrayList<ArrayList<String>> showAnnot() {
        ArrayList<ArrayList<String>> data = new ArrayList<>();
        ArrayList<String> headList = new ArrayList<>();
        try (ResultSet rs = st.executeQuery("SELECT ann_attr.id As ID, annot.start_pos AS Start, annot.end_pos AS End, type.name AS Type, annot.text as Text, attr.name AS Attrs, annot.querytext AS QueryText, text.path AS TextPath FROM annot, type, text, ann_attr, attr WHERE annot.type_id = type.id AND annot.text_id = text.id AND annot.id = ann_attr.ann_id AND ann_Attr.attr_id = attr.id ORDER BY ann_attr.id")) {
            int colCount = rs.getMetaData().getColumnCount();

            for (int j = 1; j <= colCount; j++)
                headList.add(rs.getMetaData().getColumnLabel(j));

            data.add(headList);

            while (rs.next()) {
                ArrayList<String> rowList = new ArrayList<>();
                rowList.add(rs.getString("ID"));
                rowList.add(rs.getString("Start"));
                rowList.add(rs.getString("End"));
                rowList.add(rs.getString("Type"));
                rowList.add(rs.getString("Text"));
                rowList.add(rs.getString("Attrs"));
                rowList.add(rs.getString("QueryText"));
                rowList.add(rs.getString("TextPath"));
                data.add(rowList);
            }
        } catch (SQLException e) {
            System.out.println("Error while selecting data from db: " + e.getMessage());
        } catch (NullPointerException ex) {
            System.out.println(ex.getMessage());
        }
        return data;
    }

    public void disconnect() {
        try {
            if (cn != null)
                cn.close();
            System.out.println("Disconnected from DB");
        } catch (SQLException e) {
            System.out.println("Error while closing db connection: " + e.getMessage());
        }
    }

}
